/*
 * Copyright (C) 2011 Consulanza Informatica.
 * info@consulanza.it
 *
 * This file is part of Customer Calls Tracker.
 *
 *    Customer Calls Tracker is free software: you can redistribute it and/or
 *    modify it under the terms of the GNU General Public License as published
 *    by the Free Software Foundation, either version 3 of the License, or (at 
 *    your option) any later version.
 *
 *    Customer Calls Tracker is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General 
 *    Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with Customer Calls Tracker. If not, see http://www.gnu.org/licenses/.
 *
 * Portions of this software is a derivate work from:
 *
 * Copyright (C) 2008 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package consulanza.amlsoft.customercalls;

import java.io.File;
//import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
//import java.io.InputStreamReader;
//import java.io.BufferedReader;

import org.xmlpull.v1.XmlPullParser;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.util.Xml;


public class CustomerCallsDbAdapter {
	/**
	 * utility class, buffer for DB row
	 * @author amedeo
	 *
	 */
	protected class CustomerCall {
		private String _customer;
		private String _time_end;
		private String _time_start;
		private String _notes;
		private boolean _flag_call;
		private boolean _flag_billed;

		public static final String TABLE_NAME 	= "calls";	// cliente
		/*
		 * database table fields
		 */
	    public static final String KEY_CUSTOMER 	= "customer";	// cliente
	    public static final String KEY_END 			= "time_end";	// data/ora fine intervento
	    public static final String KEY_FLAG_BILLED	= "flag_billed";// fatturato
	    public static final String KEY_FLAG_CALL	= "flag_call";	// flag chiamata
	    public static final String KEY_NOTES 		= "notes";		// note
	    public static final String KEY_START 		= "time_start";	// data/ora inizio intervento
	    public static final String KEY_ROWID 		= "_id";		// chiave primaria
	    /*
	     * Table creation sql statement
	     */
	    private static final String TABLE_CREATE =
	        "CREATE TABLE " 	+ TABLE_NAME + 
	        	" (" +
		        	KEY_ROWID 		+ " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		        	KEY_CUSTOMER 	+ " TEXT NOT NULL, " +
		            KEY_START   	+ " TEXT NOT NULL, " +
		        	KEY_END   		+ " TEXT NOT NULL, " +
		        	KEY_FLAG_CALL   + " INTEGER DEFAULT 1, " +
		        	KEY_NOTES       + " TEXT NOT NULL, " +
		        	KEY_FLAG_BILLED + " INTEGER DEFAULT 0" +
	   			");";
		
		
		protected void setCustomer (String value) {
			_customer = value;
		}
		protected void setTime_end (String value) {
			_time_end = value;
		}
		protected void setTime_start (String value) {
			_time_start = value;
		}
		protected void setNotes (String value) {
			_notes = value;
		}
		protected void setFlag_call (boolean value) {
			_flag_call = value;
		}
		protected void setFlag_call (String value) {
			if (value.equalsIgnoreCase("1")) {
				_flag_call = true;
			} else {
				_flag_call = false;
			}
		}
		protected void setFlag_billed (boolean value) {
			_flag_billed = value;
		}
		protected void setFlag_billed (String value) {
			if (value.equalsIgnoreCase("1")) {
				_flag_billed = true;
			} else {
				_flag_billed = false;
			}
		}
		protected CustomerCall() {
			_customer = "UNKNOWN";
			_time_end = "";
			_time_start = "";
			_notes = "";
			_flag_call = true;
			_flag_billed = false;
		}
		protected String getCustomer() {
			return _customer;
		}
		protected String getTime_start() {
			return _time_start;
		}
		protected String getTime_end() {
			return _time_end;
		}
		protected String getNotes() {
			return _notes;
		}
		protected boolean getFlag_call() {
			return _flag_call;
		}
		protected boolean getFlag_billed() {
			return _flag_billed;
		}
	}

    /*
     * service variables
     */
    private static final String TAG = "CustomerCallsDbAdapter";	//
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    /*
     * database and table name
     */
    private static final String DATABASE_NAME = "customercalls.db";
    private static final int 	DATABASE_VERSION = 4;
    /*
     * context
     */
    private final Context mCtx;

    /**
     * 
     * @author amedeo
     *
     */
    private class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        /**
         * create database
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CustomerCall.TABLE_CREATE);
        }
        /**
         * upgrade database:
         * 
         * 1) Exports database to XML
         * 2) drop table
         * 3) create new table
         * 4) import XML to database
         * 
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "===========================================================================");
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", will mantain all old data");
            Log.w(TAG, "--- STEP 1: export data to XML");
//        	exportXml();
            Log.w(TAG, "--- STEP 2: drop database table");
            db.execSQL("DROP TABLE IF EXISTS " + CustomerCall.TABLE_NAME);
            Log.w(TAG, "--- STEP 3: create database table: " + CustomerCall.TABLE_CREATE);
            onCreate(db);
            Log.w(TAG, "--- STEP 4: import saved XML to database");
//            importXml("/sdcard/customercalls.xml");
            Log.w(TAG, "===========================================================================");
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public CustomerCallsDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public CustomerCallsDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    /**
     * close database
     */
    public void close() {
        mDbHelper.close();
    }


    /**
     * Create a new note using the title and body provided. If the note is
     * successfully created return the new rowId for that note, otherwise return
     * a -1 to indicate failure.
     * 
     * @param customer string the customer
     * @param time_start string date/time of call start
     * @param time_end string date/time of call end
     * @param flag_call boolean whether call must be billed
     * @param notes string call notes
     * @param flag_billed boolean whether has already been billed
     * @return long the ID of inserted row or -1 if an error occurred
     */
    public long createCall(String customer, String time_start, String time_end, Boolean flag_call, String notes, Boolean flag_billed) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(CustomerCall.KEY_CUSTOMER, customer);
        initialValues.put(CustomerCall.KEY_START, time_start);
        initialValues.put(CustomerCall.KEY_END, time_end);
        initialValues.put(CustomerCall.KEY_FLAG_CALL, Util.bool2int(flag_call));
        initialValues.put(CustomerCall.KEY_NOTES, notes);
        initialValues.put(CustomerCall.KEY_FLAG_BILLED, Util.bool2int(flag_billed));

        return mDb.insert(CustomerCall.TABLE_NAME, null, initialValues);
    }

    /**
     * Delete the row with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteCall(long rowId) {

        return mDb.delete(CustomerCall.TABLE_NAME, CustomerCall.KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * Delete the row with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteBilled(boolean billed) {
    	final int value;
    	if (billed) {
    		value = 1;
    	} else {
    		value = 0;
    	}
        return mDb.delete(CustomerCall.TABLE_NAME, CustomerCall.KEY_FLAG_BILLED + "=" + value, null) > 0;
    }

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllCalls() {

        return mDb.query(CustomerCall.TABLE_NAME, new String[] {
		        		CustomerCall.KEY_ROWID, 
		        		CustomerCall.KEY_CUSTOMER,
		        		CustomerCall.KEY_START,
		        		CustomerCall.KEY_END,
		        		CustomerCall.KEY_FLAG_CALL,
		        		CustomerCall.KEY_NOTES,
		        		CustomerCall.KEY_FLAG_BILLED
        			}, null, null, null, null, null);
    }
    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchCalls(String constraints) {

        return mDb.query(CustomerCall.TABLE_NAME, new String[] {
        			CustomerCall.KEY_ROWID, 
        			CustomerCall.KEY_CUSTOMER,
        			CustomerCall.KEY_START,
        			CustomerCall.KEY_END,
        			CustomerCall.KEY_FLAG_CALL,
        			CustomerCall.KEY_NOTES,
        			CustomerCall.KEY_FLAG_BILLED
        			}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchCall(long rowId) throws SQLException {

        Cursor mCursor =

            mDb.query(true, CustomerCall.TABLE_NAME, new String[] {
            		CustomerCall.KEY_ROWID, 
            		CustomerCall.KEY_CUSTOMER,
            		CustomerCall.KEY_START,
            		CustomerCall.KEY_END,
            		CustomerCall.KEY_FLAG_CALL,
            		CustomerCall.KEY_NOTES,
            		CustomerCall.KEY_FLAG_BILLED
        			}, CustomerCall.KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param customer value to set customer to
     * @param flag_call value to set flag_call to
     * @param notes value to set notes to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateCall(long rowId, String customer, String time_start, String time_end, Boolean flag_call, String notes, Boolean flag_billed) {
        ContentValues args = new ContentValues();
        args.put(CustomerCall.KEY_CUSTOMER, customer);
        args.put(CustomerCall.KEY_START, time_start);
        args.put(CustomerCall.KEY_END, time_end);
        args.put(CustomerCall.KEY_FLAG_CALL, Util.bool2int(flag_call));
        args.put(CustomerCall.KEY_NOTES, notes);
        args.put(CustomerCall.KEY_FLAG_BILLED, Util.bool2int(flag_billed));
        return mDb.update(CustomerCall.TABLE_NAME, args, CustomerCall.KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * Update billed status of the call
     * @param rowId
     * @param flag_billed
     * @return
     */
    public boolean setBilled(long rowId, boolean flag_billed) {
    	ContentValues args = new ContentValues();
        args.put(CustomerCall.KEY_FLAG_BILLED, Util.bool2int(flag_billed));
        return mDb.update(CustomerCall.TABLE_NAME, args, CustomerCall.KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * 
     * update customer field
     * 
     * @param rowId
     * @param customer
     * @return
     */
    public boolean updateCustomer(long rowId, String customer) {
        ContentValues args = new ContentValues();
        args.put(CustomerCall.KEY_CUSTOMER, customer);
        return mDb.update(CustomerCall.TABLE_NAME, args, CustomerCall.KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * 
     * update call start time field
     * 
     * @param rowId
     * @param customer
     * @return
     */
    public boolean updateStart(long rowId, String time_start) {
        ContentValues args = new ContentValues();
        args.put(CustomerCall.KEY_START, time_start);
        return mDb.update(CustomerCall.TABLE_NAME, args, CustomerCall.KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * 
     * update call end time field
     * 
     * @param rowId
     * @param customer
     * @return
     */
    public boolean updateEnd(long rowId, String time_end) {
        ContentValues args = new ContentValues();
        args.put(CustomerCall.KEY_END, time_end);
        return mDb.update(CustomerCall.TABLE_NAME, args, CustomerCall.KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * export DB to XML
     */
    public void exportXml() {
    	final DatabaseAssistant dba = new DatabaseAssistant(mCtx,mDb);
    	dba.exportData();
    }
    /**
     * Import XML into DB
     * 
     * @param filePath
     */
    public void importXml(String filePath) {
    	
    	CustomerCall cb = null;   	
    	String curCol;
	    String curText;

    	XmlPullParser parser = Xml.newPullParser();
    	try {
    		File f = new File(filePath);
    		FileReader reader = new FileReader(f);
    	    
    	    // auto-detect the encoding from the stream
    	    parser.setInput(reader);
    	    int eventType = parser.getEventType();
    	    curCol = "";
    	    curText = "";

    	    boolean done = false;
    	    while (eventType != XmlPullParser.END_DOCUMENT && !done){
    	        String name = null;
    	        switch (eventType) {
    	            case XmlPullParser.START_DOCUMENT:
    	                break;
    	            case XmlPullParser.START_TAG:
    	                name = parser.getName();
    	                if (name.equalsIgnoreCase("row")) {
    	                	// new row, initialize DB buffer
    	                	cb = new CustomerCall();
    	                } else if (name.equalsIgnoreCase("col")) {
    	                	// column, get column name
    	                	curCol = parser.getAttributeValue(0);
    	                }
    	                break;
    	            case XmlPullParser.TEXT:
    	            	// get column value and update record buffer
    	            	curText = parser.getText();
	                	if (curCol.equalsIgnoreCase(CustomerCall.KEY_CUSTOMER)) {
	                		cb.setCustomer(curText);

	                	} else if (curCol.equalsIgnoreCase(CustomerCall.KEY_START)) {
	                		cb.setTime_start(curText);

	                	} else if (curCol.equalsIgnoreCase(CustomerCall.KEY_END)) {
	                		cb.setTime_end(curText);
	                		
	                	} else if (curCol.equalsIgnoreCase(CustomerCall.KEY_NOTES)) {
	                		cb.setNotes(curText);

	                	} else if (curCol.equalsIgnoreCase(CustomerCall.KEY_FLAG_CALL)) {
	                		cb.setFlag_call(curText);
	                		
	                	} else if (curCol.equalsIgnoreCase(CustomerCall.KEY_FLAG_BILLED)) {
	                		cb.setFlag_billed(curText);
	                	}
    	                break;

    	            case XmlPullParser.END_TAG:
    	                name = parser.getName();
    	                if (name.equalsIgnoreCase("row")) {
    	                	// end row, insert record
    	                	createCall(
    	                			cb.getCustomer(),
    	                			cb.getTime_start(),
    	                			cb.getTime_end(),
    	                			cb.getFlag_call(),
    	                			cb.getNotes(),
    	                			cb.getFlag_billed()
    	                			);
    	                } else if (name.equalsIgnoreCase("table")){
    	                    done = true;
    	                }
    	                break;
    	            }
    	        eventType = parser.next();
    	        }
    	} catch (FileNotFoundException e) {
    		Log.e(TAG, "FileNotFoundException Parsing XML", e);
    	} catch (IOException e) {
    		Log.e(TAG, "IOException Parsing XML", e);
    	} catch (Exception e){
    		Log.e(TAG, "Parsing XML", e);
    	}

    }
}